<%@ Page Language="C#" %>
<%@ OutputCache Duration="3600" VaryByParam="*" %>
<%@ Import Namespace="Teradata.Client.Provider" %>
<script runat="server">
    protected String ViewName = String.Empty;
    protected String ColumnName = String.Empty;
    protected String[] Months = { "Joe", "JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC" };
    protected String Series1 = String.Empty, Series2 = String.Empty, Series3 = String.Empty;
    protected String ConnString = ConfigurationManager.ConnectionStrings["EDW_GROUP_ID"].ConnectionString;
    
    protected void Page_Load(object sender, EventArgs e)
    {
        Response.ContentType="text/xml";
        getChartData(Request.QueryString["SQLFILTER"]);
        Response.Write(getHeader() + Series1 + Series2 + Series3 + getFooter());
    }

    protected string getHeader()
    {
        return "<vc:Chart xmlns:vc=\"clr-namespace:Visifire.Charts;assembly=SLVisifire.Charts\" Width=\"500\" Height=\"300\" BorderThickness=\"0\" Theme=\"Theme1\" View3D=\"True\" AnimationEnabled=\"True\" Watermark=\"False\" Bevel=\"False\" LightingEnabled=\"True\" ColorSet=\"Visifire1\" ShadowEnabled=\"True\" > " +
               " <vc:Chart.Titles> " +
               "  <vc:Title Text=\"Net Sales Month To Date\" FontSize=\"20\" /> " +
               " </vc:Chart.Titles> " +
               " <vc:Chart.Legends> " +
               "  <vc:Legend Enabled=\"True\" HorizontalAlignment=\"Center\" /> " +
               " </vc:Chart.Legends> " +
               " <vc:Chart.AxesX> " +
               "  <vc:Axis> " +
               "   <vc:Axis.AxisLabels> " +
               "    <vc:AxisLabels Angle=\"-45\" /> " +
               "   </vc:Axis.AxisLabels> " +
               "  </vc:Axis> " +
               " </vc:Chart.AxesX> " +
               " <vc:Chart.AxesY> " +
               "  <vc:Axis Title=\"Units Sold\" AxisType=\"Primary\" /> " +
               " </vc:Chart.AxesY> " +
               " <vc:Chart.Series> ";
    }

    protected string getFooter()
    {
        return " </vc:Chart.Series> " +
               "</vc:Chart>";
    }


    protected void getChartData(String SQLFILTER)
    {
        StringBuilder sb1 = new StringBuilder();
        StringBuilder sb2 = new StringBuilder();
        StringBuilder sb3 = new StringBuilder();

        String SQLPattern = "SELECT A.DY, A.MN, " +
               "SUM(CASE WHEN PRODUCT='IPTV' AND DIRECTION='IN' THEN 1 ELSE 0 END) - " +
               "SUM(CASE WHEN PRODUCT='IPTV' AND DIRECTION='OUT' THEN 1 ELSE 0 END) IPTV_NET, " +
               "SUM(CASE WHEN PRODUCT='HSIA' AND DIRECTION='IN' THEN 1 ELSE 0 END) - " +
               "SUM(CASE WHEN PRODUCT='HSIA' AND DIRECTION='OUT' THEN 1 ELSE 0 END) HSIA_NET, " +
               "SUM(CASE WHEN PRODUCT='VOIP' AND DIRECTION='IN' THEN 1 ELSE 0 END) - " +
               "SUM(CASE WHEN PRODUCT='VOIP' AND DIRECTION='OUT' THEN 1 ELSE 0 END) VOIP_NET, " +
               "IPTV_NET+HSIA_NET+VOIP_NET TOT_NET " +
               "FROM MIS.ACC_CAL A  " +
               "LEFT OUTER JOIN MIS.ACC_RAW B ON A.DT=B.START_DT {0} " +
               "GROUP BY 1,2 ORDER BY 1,2 ";

        sb1.Append("  <vc:DataSeries RenderAs=\"Line\" AxisYType=\"Primary\" LegendText=\"IPTV\" >  <vc:DataSeries.DataPoints> ");
        sb2.Append("  <vc:DataSeries RenderAs=\"Line\" AxisYType=\"Primary\" LegendText=\"HSIA\" >  <vc:DataSeries.DataPoints> ");
        sb3.Append("  <vc:DataSeries RenderAs=\"Line\" AxisYType=\"Primary\" LegendText=\"VOIP\" >  <vc:DataSeries.DataPoints> ");

        using (TdConnection cn = new TdConnection(ConnString))
            {
                String SQL = String.Format(SQLPattern, SQLFILTER);
                cn.Open();
                using (TdCommand cm = new TdCommand(SQL, cn))
                using (TdDataReader dr = cm.ExecuteReader())
                    while (dr.Read())
                    {
                        sb1.Append(String.Format("    <vc:DataPoint AxisXLabel=\"{1} {0}\" YValue=\"{2}\" />",
                            dr.GetInt32(0).ToString(),
                            Months[dr.GetInt32(1)],
                            dr.GetInt32(3)
                            ));
                        sb2.Append(String.Format("    <vc:DataPoint AxisXLabel=\"{1} {0}\" YValue=\"{2}\" />",
                            dr.GetInt32(0).ToString(),
                            Months[dr.GetInt32(1)],
                            dr.GetInt32(4)
                            ));
                        sb3.Append(String.Format("    <vc:DataPoint AxisXLabel=\"{1} {0}\" YValue=\"{2}\" />",
                            dr.GetInt32(0).ToString(),
                            Months[dr.GetInt32(1)],
                            dr.GetInt32(5)
                            ));
                    }
                cn.Close();
            }
        sb1.Append("   </vc:DataSeries.DataPoints>   </vc:DataSeries> ");
        sb2.Append("   </vc:DataSeries.DataPoints>   </vc:DataSeries> ");
        sb3.Append("   </vc:DataSeries.DataPoints>   </vc:DataSeries> ");

        Series1 = sb1.ToString();
        Series2 = sb2.ToString();
        Series3 = sb3.ToString();
    }
    
    
</script>

